﻿## Generic report template
## Scheduler Usage: powershell -command "& {C:\WINDOWS\Scripts\Report-Template.ps1 -SendEmail}"
## Command Line Usage (no email): .\Report-Template.ps1
## Command Line Usage (default email): .\Report-Template.ps1 -SendEmail
## Command Line Usage (one-off email): .\Report-Template.ps1 -SendEmail name@domain.com
##

param ([switch]$SendEmail , [string]$OneOffEmailAddr = "default")
# By default do not send email.
cls
#$DebugPreference = "SilentlyContinue"  #This is default
$DebugPreference = "Continue"  #This causes more output
#$DebugPreference = "Inquire"  #This causes output and queries to continue or stop
#$DebugPreference = "Stop"  #This causes output and stops


## This script produces a ______ for ________.  
## David Ray - mm/dd/yyyy
Write-Debug "Starting Report Script"
Write-Debug "Setting configuration variables"

## Setup Variables ######
#Enter addresses in array form. i.e. @("name1@dom1.com","name2@dom2.com") if none use @("")
#$defaultTo = ("name1@dom.tld","name2@dom.tld")
#$defaultCc = ("copy1@dom.tld","copy2@dom.tld")
#$defaultBcc = ("bcc1@dom.tld","bcc2@dom.tld")
$defaultTo = "david_f_ray@dell.com"
## from should be "Exchange@ps.net" (not in Array form)
$defaultFrom = "ExchangeOp@ps.net"
#the mail subject 
$subject = "CHW Transaction Log Drive Report"
#The body of the message
$emailMessage = "Transaction Log Drive Usage and Space"
#relay server
$SMTPserver = "smtp-relay.chw.edu"
$reportName = "TransactionLogReport" + $(Get-Date -Format "yyMMdd")
$reportPath = "E:\Reports\TransLogReport"
## End of Setup Variables ######

#Load up the Report Library Functions.  ReportLibrary.ps1 must be in the same dir as the report script
Write-Debug "Load Report Libary Functions"
#. ((Split-Path -Parent (Resolve-Path -Path $MyInvocation.InvocationName)) + "\ReportLibrary.ps1")
$ScriptName = $MyInvocation.MyCommand.Name
$ScriptDir = Split-Path $MyInvocation.MyCommand.Path
. "$ScriptDir\ReportLibrary.ps1"

While ($false) {
#######
## Here is the main data gathering portion of the script
Write-Debug "Running command to get data"
$2007Servers = @("chw-msg-011","chw-msg-821","chw-msg-822","chw-msg-823","chw-msg-824","chw-expf-002")
Write-Debug "$2007Servers"
$LogSpace = @()

## Loop through basic info populating ReportObject
Write-Debug "Entering loop to build data list"
ForEach ($server in $2007Servers) {
	Write-Debug "Server $server"

	$LogDriveInfo = Get-WmiObject win32_volume -ComputerName $server | Where-Object {$_.Name -match "L:\\cl.*logs"} | Select-Object Name,Capacity,Freespace
	Write-Debug "$LogDriveInfo"
	ForEach ($ldi in $LogDriveInfo) {
		$LogInfo = New-Object PSObject
		#Write-Debug "gc path: "  ("\\"+$server+"\"+$ldi.Name.replace(":","$")+"\logs\*.logs")
		#$LogCount = (Get-Childitem ("\\"+$server+"\"+$ldi.Name.replace(":","$")+"\logs")).count
		$Items = Get-Childitem ("\\"+$server+"\"+$ldi.Name.replace(":","$")+"logs\*.log")
		Write-Debug "Items $($items.count)" 
		$LogInfo | Add-Member -MemberType NoteProperty -Name Server -Value $server
		$LogInfo | Add-Member -MemberType NoteProperty -Name Path -Value $ldi.Name
		$LogInfo | Add-Member -MemberType NoteProperty -Name LogCount -Value $Items.Count
		$LogInfo | Add-Member -MemberType NoteProperty -Name OldestLog -Value ($Items | Sort -Descending LastWriteTime | Select-Object -Last 1).LastWriteTime
		$LogInfo | Add-Member -MemberType NoteProperty -Name "Capacity(gb)" -Value ("{0:N2}" -f ($ldi.Capacity/1gb))
		$LogInfo | Add-Member -MemberType NoteProperty -Name "Freespace(gb)" -Value ("{0:N2}" -f ($ldi.Freespace/1gb))
		$LogInfo | Add-Member -MemberType NoteProperty -Name PercentFree -Value ("{0:P1}" -f ($ldi.Freespace/$ldi.Capacity))
		$LogSpace += $LogInfo
	}
}

$2003Servers = @("chw-msg-301","chw-msg-302","chw-msg-303","chw-msg-401","chw-msg-402","chw-msg-403","chw-expf-001")
Write-Debug "$2003Servers"
$LogPaths = Get-StorageGroup -IncludePreExchange2007 | ? {$2003Servers -contains $_.Server} | Sort Server | Select Server,Name,LogFolderPath

ForEach ($lpath in $LogPaths) {
	Write-Debug "Server $($lpath.server)"
	$lp =  $lpath.LogFolderPath.PathName
	Write-Debug "lp $lp"
	$LogDrive = $lp.Remove($lp.IndexOf('\')+1,$lp.length-($lp.IndexOf('\')+1))
	Write-Debug "LogDrive: $Logdrive"
	$LogDriveInfo = Get-WmiObject win32_volume -ComputerName $lpath.server | Where-Object {$_.Name -eq $LogDrive} | Select-Object Name,Capacity,Freespace
	Write-Debug "Logdriveinfo $LogDriveInfo"
	ForEach ($ldi in $LogDriveInfo) {
		$LogInfo = New-Object PSObject
		$Items = Get-Childitem ("\\"+$lpath.server+"\"+$lp.replace(":","$")+"\e*.log")
		Write-Debug "Items $($items.count)" 
		#$LogCount = (Get-Childitem ("\\"+$lpath.server+"\"+$lp.replace(":","$"))).count
		$LogInfo | Add-Member -MemberType NoteProperty -Name Server -Value $lpath.server
		$LogInfo | Add-Member -MemberType NoteProperty -Name Path -Value $lp
		$LogInfo | Add-Member -MemberType NoteProperty -Name LogCount -Value $Items.Count
		$LogInfo | Add-Member -MemberType NoteProperty -Name OldestLog -Value ($Items | Sort -Descending LastWriteTime | Select-Object -Last 1).LastWriteTime
		$LogInfo | Add-Member -MemberType NoteProperty -Name "Capacity(gb)" -Value ("{0:N2}" -f ($ldi.Capacity/1gb))
		$LogInfo | Add-Member -MemberType NoteProperty -Name "Freespace(gb)" -Value ("{0:N2}" -f ($ldi.Freespace/1gb))
		$LogInfo | Add-Member -MemberType NoteProperty -Name PercentFree -Value ("{0:P1}" -f ($ldi.Freespace/$ldi.Capacity))
		$LogSpace += $LogInfo
	}
}

Write-Debug "Exiting data building loop"

$LogSpace = $LogSpace | Sort-Object PercentFree

## Output CSV of the information
Write-Debug "Saving CSV to $($reportPath)\$($reportName).csv"
$LogSpace | Export-Csv -NoTypeInformation "$($reportPath)\$($reportName).csv"
}
## Save all this out to Excel and prettify it some.

## Excel constants that will help us
Write-Debug "Call Excel Var Function"
Set-ExcelVars

# Create an instance of Excel
Write-Debug "Call function to open Excel with CSV"
Open-ExcelCSV $reportPath $reportName

# Add a little formatting to the column header
Write-Debug "Call function to format header"
Set-ExcelHeaderFormat

## Examples of conditional formatting

$bucket= $Worksheet.Range("D:D").FormatConditions.Add($xlExpression, ' "=AND(D1<(TODAY()-1),D1>(TODAY()-200) )"')
$Worksheet.Range("D:D").FormatConditions.Item(1).Interior.Color = 10284031
$Worksheet.Range("D:D").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
$Worksheet.Range("D:D").FormatConditions.Item(1).Interior.TintAndShade = 0
$Worksheet.Range("D:D").FormatConditions.Item(1).Font.Color = -16751204
$Worksheet.Range("D:D").FormatConditions.Item(1).Font.TintAndShade = 0

##$bucket= $Worksheet.Range("L:L").FormatConditions.Add($xlCellValue, $xlEqual, '="No"')
##$Worksheet.Range("L:L").FormatConditions.Item(1).Interior.Color = 13551615
##$Worksheet.Range("L:L").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
##$Worksheet.Range("L:L").FormatConditions.Item(1).Interior.TintAndShade = 0
##$Worksheet.Range("L:L").FormatConditions.Item(1).Font.Color = -16383844
##$Worksheet.Range("L:L").FormatConditions.Item(1).Font.TintAndShade = 0

##$bucket = $Worksheet.Range("L:L").FormatConditions.Add($xlCellValue, $xlEqual, '="Yes"')
##$Worksheet.Range("L:L").FormatConditions.Item(2).Interior.Color = 13561798
##$Worksheet.Range("L:L").FormatConditions.Item(2).Interior.PatternColorIndex = $xlAutomatic
##$Worksheet.Range("L:L").FormatConditions.Item(2).Interior.TintAndShade = 0
##$Worksheet.Range("L:L").FormatConditions.Item(2).Font.Color = -16752384
##$Worksheet.Range("L:L").FormatConditions.Item(2).Font.TintAndShade = 0

# Make Columns wide enough for content
Write-Debug "Calling function to autofit columns"
Set-ExcelAutofitColumns

#Save the Excel File. Use format $xlOpenXMLWorkbook for normal xlsx and $xlExcel12 for xlsb
Write-Debug "Calling function to save file"
Save-Excel $reportPath $reportName $xlOpenXMLWorkbook
#Save-Excel makes available $reportFullName for sending

## Send Mail
if ($sendEmail.IsPresent) {
	Write-Debug "Calling send email function"
	Send-Email $OneOffEmailAddr $defaultTo $defaultCc $defaultBcc $defaultFrom `
					$subject $emailmessage $reportFullName $SMTPserver
}
Write-Debug "End of Report script"